﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaintenanceConsistencyCheck]
#-- Purpose:		Checks for data errors in the database
#--	Last Update:	08/29/2011
#--					For a complete history - please review comments in Version
#--					control.
#-- Called By:		dbo.spMaintenanceConsistencyCheckAll
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaintenanceConsistencyCheck]
(
	@database_name	sysname
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE @sCommand varchar(8000), @offline bit, @readonly bit

--- Do Not Process tempdb
IF @database_name = 'tempdb'
	RETURN

--- Determine the database state
EXEC	@offline = dbo.spIsDatabaseOffline @database_name
EXEC	@readonly = dbo.spIsDatabaseReadOnly @database_name

--- Only attempt the backup on an ONLINE database
IF @offline = 0 AND @readonly = 0
  BEGIN
	BEGIN TRY
		SELECT	@sCommand = 'USE [' + @database_name + ']; DBCC CHECKDB WITH NO_INFOMSGS;'
		EXEC	(@sCommand)
	END TRY
	BEGIN CATCH
		--- Log the error - if possible
		DECLARE @sResults varchar(8000)
		SELECT @sResults = 'The database ' + @database_name + ' failed to perform maintenance (' + @sCommand + ').'
		IF ERROR_NUMBER() IS NOT NULL
			SELECT @sResults = @sResults + CHAR(13) 
							+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
							+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
							+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
							+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), '')

		--- Print the error message to sysout
		PRINT @sResults

		--- Insert the error in the database
		INSERT INTO dbo.tblMaintenance (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
			VALUES (@database_name, GETDATE(), 'checkdb', 0, @sResults)
	END CATCH
  END
ELSE
	PRINT 'The database ' + @database_name + ' is not in ONLINE mode or is READ-ONLY'

SET NOCOUNT OFF
